﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Caching;
using LSUtility;
using MySql.Data.MySqlClient;

namespace LSData
{
    public class DataBase
    {
        #region Conn
        protected static readonly string EConn = ConfigurationManager.ConnectionStrings["LSEducation"].ToString().Trim();
        protected static readonly string AConn = ConfigurationManager.ConnectionStrings["LSAccount"].ToString().Trim();
        #endregion

        #region 从DataReader中获取List实例集合
        /// <summary>
        /// 从DataReader中获取List实例集合
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static IEnumerable<T> GetListFromDataReader<T>(MySqlDataReader dr) where T : new()
        {
            var list = new List<T>();
            var t = new T();
            var typeName = t.GetTypeAndName();
            if (dr != null && dr.HasRows)
            {
                while (dr.Read())
                {
                    try
                    {
                        t = new T();
                        foreach (var tn in typeName)
                        {
                            if (tn.Tt2 == typeof(string))
                            {
                                var value = MySqlDataReaderGetValueByString(dr, tn.Tt1);
                                if (!String.IsNullOrEmpty(value))
                                    t.PropertySetValue(tn.Tt1, value);
                            }
                            else if (tn.Tt2 == typeof(int))
                            {
                                var value = MySqlDataReaderGetValueByInt(dr, tn.Tt1);
                                if (value.HasValue)
                                    t.PropertySetValue(tn.Tt1, value.Value);
                            }
                            else if (tn.Tt2 == typeof(DateTime))
                            {
                                var value = MySqlDataReaderGetValueByDateTime(dr, tn.Tt1);
                                if (value.HasValue)
                                    t.PropertySetValue(tn.Tt1, value.Value);
                            }
                            else if (tn.Tt2 == typeof(bool))
                            {
                                var value = MySqlDataReaderGetValueByBoolen(dr, tn.Tt1);
                                if (value.HasValue)
                                    t.PropertySetValue(tn.Tt1, value.Value);
                            }
                            else if (tn.Tt2 == typeof(short))
                            {
                                var value = MySqlDataReaderGetValueByShort(dr, tn.Tt1);
                                if (value.HasValue)
                                    t.PropertySetValue(tn.Tt1, value.Value);
                            }
                            else if (tn.Tt2 == typeof(decimal))
                            {
                                var value = MySqlDataReaderGetValueByDecimal(dr, tn.Tt1);
                                if (value.HasValue)
                                    t.PropertySetValue(tn.Tt1, value.Value);
                            }
                            else if (tn.Tt2.IsEnum)
                            {
                                var value = MySqlDataReaderGetValueByInt(dr, tn.Tt1);
                                if (value.HasValue)
                                    t.PropertySetValue(tn.Tt1, Enum.Parse(tn.Tt2, value.Value.ToString(CultureInfo.InvariantCulture)));

                            }
                        }
                        list.Add(t);
                    }
                    catch
                    {
                        continue;
                    }
                }

                dr.Close();
                dr.Dispose();
            }
            return list;
        }
        #endregion

        #region MySqlDataReaderGetValue
        protected static int? MySqlDataReaderGetValueByInt(MySqlDataReader dr, string fieldName)
        {
            try
            {
                return !dr.IsDBNull(dr.GetOrdinal(fieldName)) ? dr.GetInt32(fieldName) : (int?)null;
            }
            catch (Exception)
            {
                return null;
            }
        }

        protected static short? MySqlDataReaderGetValueByShort(MySqlDataReader dr, string fieldName)
        {
            try
            {
                return !dr.IsDBNull(dr.GetOrdinal(fieldName)) ? dr.GetInt16(fieldName) : (short?)null;
            }
            catch (Exception)
            {
                return null;
            }
        }

        protected static decimal? MySqlDataReaderGetValueByDecimal(MySqlDataReader dr, string fieldName)
        {
            try
            {
                return !dr.IsDBNull(dr.GetOrdinal(fieldName)) ? dr.GetDecimal(fieldName) : (decimal?)null;
            }
            catch (Exception)
            {
                return null;
            }
        }

        protected static bool? MySqlDataReaderGetValueByBoolen(MySqlDataReader dr, string fieldName)
        {
            try
            {
                return !dr.IsDBNull(dr.GetOrdinal(fieldName)) ? dr.GetBoolean(fieldName) : (bool?)null;
            }
            catch (Exception)
            {
                return null;
            }
        }

        protected static string MySqlDataReaderGetValueByString(MySqlDataReader dr, string fieldName)
        {
            try
            {
                return !dr.IsDBNull(dr.GetOrdinal(fieldName)) ? dr.GetString(fieldName) : null;
            }
            catch (Exception)
            {
                return null;
            }
        }

        protected static DateTime? MySqlDataReaderGetValueByDateTime(MySqlDataReader dr, string fieldName)
        {
            try
            {
                return !dr.IsDBNull(dr.GetOrdinal(fieldName)) ? dr.GetDateTime(fieldName) : (DateTime?)null;
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region 创建 MySql 参数实例 --- MakeParameter
        /// <summary>
        /// 创建 MySql 参数实例
        /// </summary>
        /// <param name="paramName">参数名</param>
        /// <param name="dbType">数据库类型</param>
        /// <param name="paramValue">参数值</param>
        /// <returns></returns>
        protected static MySqlParameter MakeParameter(String paramName, MySqlDbType dbType, object paramValue)
        {
            return new MySqlParameter
            {
                ParameterName = paramName,
                MySqlDbType = dbType,
                Value = paramValue
            };
        }
        #endregion

        #region 获取Sql语句的 Limit 条件部分 --- LimitStr
        /// <summary>
        /// 获取Sql语句的 Limit 条件部分
        /// </summary>
        /// <param name="pageNum">页码。从1开始，若不分页则传 -1</param>
        /// <param name="pageSize">页大小</param>
        /// <returns></returns>
        private static String LimitStr(int pageNum, int pageSize)
        {
            if (pageNum != -1)
            {
                var index = pageSize * (pageNum - 1);
                if (index < 0)
                {
                    index = 0;
                }
                return String.Format(" Limit {0},{1}", index, pageSize);
            }

            return "";
        }
        #endregion

        #region 获取列表
        /// <summary>
        /// 获取列表，注意查询的字段必须和实体类的名字一致
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="conn"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        protected static IEnumerable<T> GetList<T>(string sql, string conn, IList<MySqlParameter> parameter = null) where T : new()
        {
            using (var provider = new MySqlProvider(conn))
            {
                using (var cmd = provider.GetCommand(sql))
                {
                    cmd.Parameters.Clear();
                    if (parameter != null) cmd.Parameters.AddRange(parameter.ToArray());
                    return GetListFromDataReader<T>(provider.ExecuteDataReader(cmd));
                }
            }
        }

        /// <summary>
        /// 获取列表，注意查询的字段必须和实体类的名字一致
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="getCount"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        protected static IEnumerable<T> GetList<T>(string sql, string conn, int getCount) where T : new()
        {
            using (var provider = new MySqlProvider(conn))
            {
                sql += String.Format(" Limit 0,{0}", getCount);
                using (var cmd = provider.GetCommand(sql))
                {
                    return GetListFromDataReader<T>(provider.ExecuteDataReader(cmd));
                }
            }
        }

        /// <summary>
        /// 获取列表，并进入系统缓存，时间为分钟，注意查询的字段必须和实体类的名字一致
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="conn"></param>
        /// <param name="cacheName"></param>
        /// <param name="cacheTimeMinutes"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        protected static List<T> GetList<T>(string sql, string conn, string cacheName, int cacheTimeMinutes, IList<MySqlParameter> parameter = null) where T : new()
        {
            if (String.IsNullOrEmpty(cacheName) || cacheTimeMinutes <= 0) return GetList<T>(sql, conn).ToList();

            var rData = HttpRuntime.Cache[cacheName] as List<T>;
            if (rData == null || rData.Count <= 0)
            {
                rData = GetList<T>(sql, conn, parameter).ToList();
                HttpRuntime.Cache.Add(cacheName, rData, null, Cache.NoAbsoluteExpiration,
                                      TimeSpan.FromMinutes(cacheTimeMinutes), CacheItemPriority.High, null);
            }
            return rData;
        }

        /// <summary>
        /// 获取列表，并进入系统缓存，时间为分钟，注意查询的字段必须和实体类的名字一致
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="conn"></param>
        /// <param name="cacheName"></param>
        /// <param name="getCount"></param>
        /// <param name="cacheTimeMinutes"></param>
        /// <returns></returns>
        protected static List<T> GetList<T>(string sql, string conn, string cacheName, int cacheTimeMinutes, int getCount) where T : new()
        {
            sql += String.Format(" Limit 0,{0}", getCount);
            if (String.IsNullOrEmpty(cacheName) || cacheTimeMinutes <= 0) return GetList<T>(sql, conn).ToList();

            var rData = HttpRuntime.Cache[cacheName] as List<T>;
            if (rData == null || rData.Count <= 0)
            {
                rData = GetList<T>(sql, conn).ToList();
                HttpRuntime.Cache.Add(cacheName, rData, null, Cache.NoAbsoluteExpiration,
                                      TimeSpan.FromMinutes(cacheTimeMinutes), CacheItemPriority.High, null);
            }
            return rData;
        }

        #endregion

        #region 获取实例
        /// <summary>
        /// 获取实例，注意查询的字段必须和实体类的名字一致
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="conn"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        protected static T GetInfo<T>(string sql, string conn, IList<MySqlParameter> parameter = null) where T : new()
        {
            using (var provider = new MySqlProvider(conn))
            {
                using (var cmd = provider.GetCommand(sql))
                {
                    try
                    {
                        cmd.Parameters.Clear();
                        if (parameter != null) cmd.Parameters.AddRange(parameter.ToArray());
                        return GetListFromDataReader<T>(provider.ExecuteDataReader(cmd)).FirstOrDefault();
                    }
                    catch (Exception)
                    {
                        return default(T);
                    }
                }
            }
        }
        #endregion

        #region 分页列表
        /// <summary>分页列表
        /// 分页列表，注意查询的字段必须和实体类的名字一致
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="listSql">列表的Sql语句</param>
        /// <param name="countSql">获取总数的Sql语句</param>
        /// <param name="count">返回查询的数量，当传入为-1或分页数大于1时，不返回查询的总数；当传入为-2时，始终返回查询的总数；当出入-3时，只返回查询查询总数，不返回列表；</param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static List<T> GetList<T>(int pageSize, int pageIndex, string listSql, string countSql, ref int count, string conn) where T : new()
        {
            var sqlStr = new StringBuilder(listSql);
            sqlStr.Append(LimitStr(pageIndex, pageSize));
            if (count == -3 || count == -2 || (count != -1 && pageIndex <= 1))
            {
                using (var provider = new MySqlProvider(conn))
                {
                    using (var cmd = provider.GetCommand(countSql))
                    {
                        cmd.Parameters.Clear();
                        count = provider.ExecuteScalar(cmd).Obj2Int();
                    }
                }
            }

            if (count != -3)
            {
                using (var provider = new MySqlProvider(conn))
                {
                    using (var cmd = provider.GetCommand(sqlStr.ToString()))
                    {
                        cmd.Parameters.Clear();
                        return GetListFromDataReader<T>(provider.ExecuteDataReader(cmd)).ToList();
                    }
                }
            }
            return new List<T>();
        }
        #endregion

        #region 数据插入
        /// <summary>
        /// 数据插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="insertGroup">在Insert属性中标记的Group的值</param>
        /// <param name="conn"></param>
        /// <param name="isReturnId"></param>
        /// <returns></returns>
        public static int Insert<T>(T t, string insertGroup, string conn, bool isReturnId)
        {
            using (var mp = new MySqlProvider(conn))
            {
                using (var cmd = mp.GetCommand())
                {
                    ICollection<MySqlParameter> paramList;
                    cmd.CommandText = t.InsertStrGet(insertGroup, out paramList);
                    if (isReturnId) cmd.CommandText += ";SELECT LAST_INSERT_ID();";
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddRange(paramList.ToArray());
                    return isReturnId ? mp.ExecuteScalar(cmd).Obj2Int(0) : mp.ExecuteNonQuery(cmd).Obj2Int(0);
                }
            }
        }
        /// <summary>
        /// 数据插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="conn"></param>return
        /// <returns></returns>
        public static int InsertReturnId<T>(T t, string conn)
        {
            return Insert(t, String.Empty, conn, true);
        }
        /// <summary>
        /// 数据插入(默认Education数据库)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static int InsertReturnId<T>(T t)
        {
            return Insert(t, String.Empty, EConn, true);
        }

        /// <summary>
        /// 数据插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static bool Insert<T>(T t, string conn)
        {
            return Insert(t, String.Empty, conn, false) > 0;
        }
        /// <summary>
        /// 数据插入(默认Education数据库)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <returns></returns>
        public static bool Insert<T>(T t)
        {
            return Insert(t, String.Empty, EConn, false) > 0;
        }

        #endregion

        #region 数据更新
        /// <summary>
        /// 数据更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="where"></param>
        /// <param name="insertGroup">在Insert属性中标记的Group的值</param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static bool Update<T>(T t, string where, string insertGroup, string conn)
        {
            using (var mp = new MySqlProvider(conn))
            {
                using (var cmd = mp.GetCommand())
                {
                    ICollection<MySqlParameter> paramList;
                    cmd.CommandText = t.InsertStrGet(insertGroup, out paramList) + " " + where;
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddRange(paramList.ToArray());
                    return mp.ExecuteNonQuery(cmd) > 0;
                }
            }
        }

        /// <summary>
        /// 数据更新
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="t"></param>
        /// <param name="where"></param>
        /// <param name="conn"></param>
        /// <returns></returns>
        public static bool Update<T>(T t, string where, string conn)
        {
            return Update(t, where, String.Empty, conn);
        }

        #endregion

        #region 执行一条Sql语句
        /// <summary>
        /// 执行一条Sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="conn"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static int ExecSql(string sql, string conn, IList<MySqlParameter> parameter = null)
        {
            using (var mp = new MySqlProvider(conn))
            {
                using (var cmd = mp.GetCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.Clear();
                    if (parameter != null) cmd.Parameters.AddRange(parameter.ToArray());
                    return mp.ExecuteNonQuery(cmd);
                }
            }
        }
        #endregion

        #region 返回首行首列
        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="conn"></param>
        /// <param name="parameter"></param>
        /// <returns></returns>
        public static string ExecScalar(string sql, string conn, IList<MySqlParameter> parameter = null)
        {
            using (var mp = new MySqlProvider(conn))
            {
                using (var cmd = mp.GetCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.Clear();
                    if (parameter != null) cmd.Parameters.AddRange(parameter.ToArray());
                    return mp.ExecuteScalar(cmd);
                }
            }
        }
        #endregion
    }
}
